/*
 * Copyright 2011 Subhabrata Ghosh <subho.ghosh at langene.net>.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */



package net.langene.nlp.utils;

//~--- non-JDK imports --------------------------------------------------------

import net.langene.nlp.Context;

//~--- JDK imports ------------------------------------------------------------

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

//~--- classes ----------------------------------------------------------------

/**
 *
 * @author Subhabrata Ghosh <subho.ghosh at langene.net>
 */
public class DBHelper {

    /**
     * Insert/Update the Entity correlation record to the DB.
     *
     * @param entity - Entity
     * @param relationto - Related word(s)
     * @param relationtype - Relation type
     * @throws Exception
     */
    public static void addEntityFormRecord(String entity, String relationto, String relationtype)
            throws Exception {
        Connection		dbconn    = Context.get().getConnection();
        String			key       = createkey(entity, relationto, relationtype);
        String			selectsql = "SELECT samplecount FROM langene_nlp.entityforms " + " WHERE id = ? ";
        PreparedStatement	pstmnt    = dbconn.prepareStatement(selectsql);

        pstmnt.setString(1, key);

        long		count = -1;
        ResultSet	rs    = pstmnt.executeQuery();

        while (rs.next()) {
            count = rs.getLong(1);

            break;
        }

        rs.close();
        pstmnt.close();

        if (count > 0) {
            String	updatesql = "UPDATE langene_nlp.entityforms SET samplecount = ? "
                               + " WHERE id = ? ";

            pstmnt = dbconn.prepareStatement(updatesql);
            pstmnt.setLong(1, count + 1);
            pstmnt.setString(2, key);
            pstmnt.executeUpdate();
            pstmnt.close();
        } else {
            String	insertsql = "INSERT INTO langene_nlp.entityforms "
                               + " ( id, entity, relationto, relationtype, samplecount ) "
                               + " VALUES ( ?, ?, ?, ?, ?) ";

            pstmnt = dbconn.prepareStatement(insertsql);
            pstmnt.setString(1, key);
            pstmnt.setString(2, entity);
            pstmnt.setString(3, relationto);
            pstmnt.setString(4, relationtype);
            pstmnt.setLong(5, 1);
            pstmnt.executeUpdate();
            pstmnt.close();
        }
    }

    /**
     * Insert/Update the Action correlation record to the DB.
     *
     * @param action Action
     * @param relationto Related to word(s)
     * @param relationtype Relation type.
     * @throws Exception
     */
    public static void addActionFormRecord(String action, String relationto, String relationtype)
            throws Exception {
        Connection		dbconn    = Context.get().getConnection();
        String			key       = createkey(action, relationto, relationtype);
        String			selectsql = "SELECT samplecount FROM langene_nlp.actionforms " + " WHERE id = ? ";
        PreparedStatement	pstmnt    = dbconn.prepareStatement(selectsql);

        pstmnt.setString(1, key);

        long		count = -1;
        ResultSet	rs    = pstmnt.executeQuery();

        while (rs.next()) {
            count = rs.getLong(1);

            break;
        }

        rs.close();
        pstmnt.close();

        if (count > 0) {
            String	updatesql = "UPDATE langene_nlp.actionforms SET samplecount = ? "
                               + " WHERE id = ? ";

            pstmnt = dbconn.prepareStatement(updatesql);
            pstmnt.setLong(1, count + 1);
            pstmnt.setString(2, key);
            pstmnt.executeUpdate();
            pstmnt.close();
        } else {
            String	insertsql = "INSERT INTO langene_nlp.actionforms "
                               + "( id, action, relationto, relationtype, samplecount ) "
                               + " VALUES ( ?, ?, ?, ?, ? ) ";

            pstmnt = dbconn.prepareStatement(insertsql);
            pstmnt.setString(1, key);
            pstmnt.setString(2, action);
            pstmnt.setString(3, relationto);
            pstmnt.setString(4, relationtype);
            pstmnt.setLong(5, 1);
            pstmnt.executeUpdate();
            pstmnt.close();
        }
    }

    private static String createkey(String name, String relation, String type) {
        String key = name + "__" + relation + "__" + type;
	return key.replaceAll("\\W", "__");
    }
}


//~ Formatted by Jindent --- http://www.jindent.com
